package com.gmail.liulijuan.DB;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class CustomDao {
	public Custom findByID(int id){
	ResultSet rs = null;
	Custom ct= null;
	
	try {
		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		String url = "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=mydb.mdb";
		Connection conn = DriverManager.getConnection(url, "", "");
		Statement stmt = conn.createStatement();
		rs = stmt.executeQuery("SELECT * FROM Customwhere ID=" + id);
		ct = new Custom();
		while (rs.next()) {
			
			ct.setID(rs.getInt("ID"));
			ct.setCustomName(rs.getString("CustomName"));
			ct.setSex(rs.getString("Sex"));
			ct.setPhoneNumber(rs.getString("PhoneNumber"));
			
			
		}

		rs.close();

		stmt.close();
		conn.close();

	} catch (Exception ex) {
		ex.printStackTrace();
	}
	return ct;
}
	
	public ArrayList addAllCustom(){
		ResultSet rs = null;
		Custom ct= null;
		ArrayList customal = new ArrayList();
		
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			String url = "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=mydb.mdb";
			Connection conn = DriverManager.getConnection(url, "", "");
			Statement stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM Custom");

			while (rs.next()) {
				
				ct = new Custom();
				ct.setID(rs.getInt("ID"));
				ct.setCustomName(rs.getString("CustomName"));
				ct.setSex(rs.getString("Sex"));
				ct.setPhoneNumber(rs.getString("PhoneNumber"));
				
				customal.add(ct);
			}

			rs.close();

			stmt.close();
			conn.close();

		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return customal;
	}
	public int InsertCustom(Custom ct){
		int result=0;
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			String url = "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=mydb.mdb";
			Connection conn = DriverManager.getConnection(url, "", "");
			Statement stmt = conn.createStatement();

			String sql="insert into Employee(EmployeeName,Password,Sex,Age,Role,Phonenumber) values('"
				+ct.getCustomName()
				+"','"
				
				+ct.getSex()
				+"',"
				
				+ct.getPhoneNumber()
				+"')";
			PreparedStatement ps = conn
					.prepareStatement(sql);

			result=ps.executeUpdate();

			ps.close();

			stmt.close();
			conn.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return result;
	}
	
	public int UpdataCustom(Custom ct){
		int result=0;
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			String url = "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=mydb.mdb";
			Connection conn = DriverManager.getConnection(url, "", "");
			Statement stmt = conn.createStatement();

			String sql="update Employee set EmployeeName=?,Password=?,Sex=?,Age=?,Role=?,Phonenumber=? where id=? ";
			PreparedStatement ps = conn
					.prepareStatement(sql);
			ps.setString(1,ct.CustomName);
			
			ps.setString(3,ct.Sex);
			
			
			ps.setString(6,ct.PhoneNumber);
			ps.setInt(7,ct.ID);

			result=ps.executeUpdate();

			ps.close();

			stmt.close();
			conn.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return result;
	}

}



